Kerry Osborne's Oracle Blog

Ever wonder why that weird line shows up in the Predicate Section of an execution plan on Exadata? Me too! The STORAGE bit tells us it’s a filter applied at the storage cell layer, but the rest is nonsensical. Well I recently ran across a very brief mention of it in a Metalink note. (I know it’s not called Metalink any more, but I’m kind of set in my ways). The note said it was related to distribution of rows to PX slaves. Ah ha! Let’s test it out. Here’s a plan with the predicate just so you can see what it looks like.

KSO@arcp> @dplan
Enter value for sql_id: a9axwj6ym3b29
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a9axwj6ym3b29, child number 0
-------------------------------------
select /*+  parallel_index(t, "ISD_SI_I03",8)  dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring no_substrb_pad  no_expand index_ffs(t,"ISD_SI_I03") */
count(*) as nrw,count(distinct sys_op_lbid(725425,'L',t.rowid)) as
nlb,count(distinct hextoraw(sys_op_descend("SUPPORT_LEVEL")||sys_op_desc
end("SUPPORT_LEVEL_KEY")||sys_op_descend("NAME")||sys_op_descend("VALUE"
))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from
"ISD"."SUPPORT_INFO" sample block (   .0503530742,1)  t where
"SUPPORT_LEVEL" is not null or "SUPPORT_LEVEL_KEY" is not null or
"NAME" is not null or "VALUE" is not null

Plan hash value: 1766555783

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |            |       |       |   672 (100)|          |        |      |            |
|   1 |  SORT GROUP BY                              |            |     1 |    56 |            |          |        |      |            |
|   2 |   PX COORDINATOR                            |            |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)                      | :TQ10001   |     1 |    56 |            |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT GROUP BY                           |            |     1 |    56 |            |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                             |            |     1 |    56 |            |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH                          | :TQ10000   |     1 |    56 |            |          |  Q1,00 | P->P | HASH       |
|   7 |        SORT GROUP BY                        |            |     1 |    56 |            |          |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR                   |            |   397K|    21M|   672   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |          INDEX STORAGE SAMPLE FAST FULL SCAN| ISD_SI_I03 |   397K|    21M|   672   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - storage(:Z>=:Z AND :Z<=:Z AND ("SUPPORT_LEVEL" IS NOT NULL OR "SUPPORT_LEVEL_KEY" IS NOT NULL OR "NAME" IS NOT NULL OR
              "VALUE" IS NOT NULL))
       filter(("SUPPORT_LEVEL" IS NOT NULL OR "SUPPORT_LEVEL_KEY" IS NOT NULL OR "NAME" IS NOT NULL OR "VALUE" IS NOT NULL))


37 rows selected.

Notice that the plan is for a PX statement. So how can we convince ourselves that it is a PX related predicate. We can try two tests.

1. Let's see if we can find any SQL statements that have the predicate that aren't PX statements.
2. Let's see if we can find any PX statements that don't have the predicate.

So here we go.

KSO@arcp>  -- How many :Z>=:Z's are there?
KSO@arcp> select count(*) from v$sql_plan
  2  where ACCESS_PREDICATES like '%:Z>=:Z%'
  3  /

  COUNT(*)
----------
        73

1 row selected.

KSO@arcp>  -- How many :Z>=:Z's are there that are not PX?
KSO@arcp> select count(*) from v$sql_plan
  2  where ACCESS_PREDICATES like '%:Z>=:Z%'
  3  and sql_id not in (select sql_id from v$sql_plan where operation like 'PX%')
  4  /

  COUNT(*)
----------
         0

1 row selected.

KSO@arcp>  -- How many PX's don't have :Z>=:Z?
KSO@arcp> select count(distinct sql_id) from v$sql_plan
  2  where operation like 'PX%'
  3  and sql_id not in (select sql_id from v$sql_plan
  4  where ACCESS_PREDICATES like '%:Z>=:Z%')
  5  /

  COUNT(*)
----------
       154

1 row selected.

KSO@arcp>  -- Whoa, that's a little unexpected!

So there are none of the :Z>=:Z predicates on non-PX queries but there are a bunch of PX queries that don't have the predicate. Let's look at a couple of those and see why those might not have the predicate.

KSO@arcp> -- Get SQL_ID's for PX's that don't have :Z>=:Z
KSO@arcp> select distinct sql_id from v$sql_plan
  2  where operation like 'PX%'
  3  and sql_id not in (select sql_id from v$sql_plan
  4  where ACCESS_PREDICATES like '%:Z>=:Z%')
  5  /

SQL_ID
-------------
7xa3zbpgkbta7
ftkmqqq3ga0nf
6wgmq24t9xy6f
7tsf3h3qjth77
fg1aphaqvcmb3
...
bt9n0qsg8k4sb
fffrvvnrnmztg
97x3zj2fb0y5z
5dudhrch3sv8r
bbw31mhra7ryu

154 rows selected.

KSO@arcp> @dplan
KSO@arcp> set lines 150
KSO@arcp> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
  2  /
Enter value for sql_id: bbw31mhra7ryu
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bbw31mhra7ryu, child number 0
-------------------------------------
select MUTEX_TYPE, LOCATION, SLEEPS, WAIT_TIME from GV$MUTEX_SLEEP
where INST_ID = USERENV('INSTANCE')

Plan hash value: 365768877

----------------------------------------------------------------------------
| Id  | Operation            | Name           |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |        |      |            |
|*  1 |  PX COORDINATOR      |                |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000       |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW              | GV$MUTEX_SLEEP |  Q1,00 | PCWP |            |
|   4 |     FIXED TABLE FULL | X$MUTEX_SLEEP  |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INST_ID"=USERENV('INSTANCE'))
   3 - filter("INST_ID"=USERENV('INSTANCE'))

Note
-----
   - rule based optimizer used (consider using cbo)


27 rows selected.

KSO@arcp> 
KSO@arcp> /
Enter value for sql_id: 5dudhrch3sv8r
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5dudhrch3sv8r, child number 0
-------------------------------------
select  NAMESPACE , GETS , GETHITS , GETHITRATIO , PINS , PINHITS ,
PINHITRATIO , RELOADS , INVALIDATIONS , DLM_LOCK_REQUESTS ,
DLM_PIN_REQUESTS , DLM_PIN_RELEASES , DLM_INVALIDATION_REQUESTS ,
DLM_INVALIDATIONS from GV$LIBRARYCACHE where inst_id =
USERENV('Instance')

Plan hash value: 3363616119

-----------------------------------------------------------------------------
| Id  | Operation            | Name            |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |        |      |            |
|*  1 |  PX COORDINATOR      |                 |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000        |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW              | GV$LIBRARYCACHE |  Q1,00 | PCWP |            |
|*  4 |     FIXED TABLE FULL | X$KGLST         |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INST_ID"=USERENV('INSTANCE'))
   3 - filter("INST_ID"=USERENV('INSTANCE'))
   4 - filter((LENGTH("KGLSTDSC")<=15 AND "KGLSTGET"<>0 AND
              "KGLSTTYP"='NAMESPACE'))

Note
-----
   - rule based optimizer used (consider using cbo)


32 rows selected.

So both of those queries are against Fixed Tables (memory structures). Makes sense that a filter at the storage layer would not be necessary. As it turns out, all 154 of the PX queries that don't have the :Z>=:Z predicate are queries against Fixed Tables. Here's a quick check in case you want to try it on your own Exadata.

KSO@arcp> select count(*) from v$sql_plan
  2  where operation like 'PX%'
  3  and sql_id not in (select sql_id from v$sql_plan
  4  where ACCESS_PREDICATES like '%:Z>=:Z%')
  5  and sql_id not in (select sql_id from v$sql_plan
  6  where operation not like 'FIXED%')
  7  /

  COUNT(*)
----------
         0

1 row selected.

So while not a definitive proof, it does appear that the :Z>=:Z predicate is related to PX row distribution. Now if I can just figure out why the storage filters are always repeated in the XPLAN output as a standard filter for offloaded queries like in this example:

Enter value for sql_id: 0qa98gcnnza7h
Enter value for child_no: 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0qa98gcnnza7h, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 > 0

Plan hash value: 568322376

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("COL1">0)
       filter("COL1">0)


20 rows selected.

Let me know if you have the answer to that one!